﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace IPBoss.Copyright.UtilityHelper
{
    public class AsposeCellsHelper
    {

        private Workbook _Workbook = null;
        protected Log4NetHelper LogHelper { get { return Log4NetHelper.Create(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); } }
        Style style1 = null;
        Style style2 = null;
        public AsposeCellsHelper()
        {
            _Workbook = new Workbook();
            _Workbook.Worksheets.RemoveAt(0);
            style1 = _Workbook.Styles[_Workbook.Styles.Add()];

            style1.HorizontalAlignment = TextAlignmentType.Center;
            style1.Font.Name = "宋体";
            style1.Font.Size = 16;
            style1.IsLocked = true;
            style1.Font.IsBold = true;
            style1.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);
            style1.Pattern = BackgroundType.Solid;
            style1.IsTextWrapped = true;
            style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style1.VerticalAlignment = TextAlignmentType.Center;
            style1.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
            style1.Pattern = BackgroundType.Solid;

            style2 = _Workbook.Styles[_Workbook.Styles.Add()];

            style2.HorizontalAlignment = TextAlignmentType.Center;
            style2.Font.Name = "宋体";
            style2.Font.Size = 11;
            style2.IsLocked = true;
            style2.Font.IsBold = false;
            style2.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);
            style2.Pattern = BackgroundType.Solid;
            style2.IsTextWrapped = true;
            style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style2.VerticalAlignment = TextAlignmentType.Center;
            style2.Pattern = BackgroundType.Solid;
        }

        /// <summary>
        /// 初始化Execl操作类
        /// </summary>
        /// <param name="file">现有的execl文件</param>
        public AsposeCellsHelper(string file)
        {
            _Workbook = new Workbook(file);
        }

        /// <summary>
        /// 添加Sheet
        /// </summary>
        /// <param name="name">Sheet名称</param>
        /// <returns>Sheet的索引</returns>
        public int AddWorksheet(string name)
        {
            var worksheet = _Workbook.Worksheets.Find(t => t.Name == name);
            if (worksheet != null)
                return worksheet.Index;
            worksheet = _Workbook.Worksheets.Add(name);
            if (worksheet != null)
                return worksheet.Index;
            return -1;
        }

        /// <summary>
        /// 获取sheet的索引
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int GetSheetIndex(string sheetName)
        {


            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentNullException("sheetName 必须有值！");
            return _Workbook.Worksheets.FindIndex(t => t.Name == sheetName);
        }

        public int GetSheetCellRowCount(int sheetIndex)
        {


            if (sheetIndex >= _Workbook.Worksheets.Count)
                throw new IndexOutOfRangeException("Worksheets 的最大数为:" + _Workbook.Worksheets.Count);
            return _Workbook.Worksheets[sheetIndex].Cells.Rows.Count;
        }


        public int GetSheetCellColumnCount(int sheetIndex)
        {


            if (sheetIndex >= _Workbook.Worksheets.Count)
                throw new IndexOutOfRangeException("Worksheets 的最大数为:" + _Workbook.Worksheets.Count);
            return _Workbook.Worksheets[sheetIndex].Cells.MaxColumn;
        }
        public void ImportArray(int sheetIndex, string[] array, int firstRow = 0, int firstColumn = 0)
        {
            Worksheet sheet = _Workbook.Worksheets[sheetIndex];
            sheet.Cells.ImportArray(array, firstRow, firstColumn, false);
        }


        /// <summary>
        /// 导入数据到Sheet
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="dt">书记表</param>
        /// <returns></returns>
        public void ImportDataTable(int sheetIndex, System.Data.DataTable dt)
        {
            Worksheet sheet = _Workbook.Worksheets[sheetIndex];
            sheet.Cells.ImportDataTable(dt, true, 0, 0);
            sheet.Cells.CreateRange(0, 0, 1, dt.Columns.Count).SetStyle(style1);
            sheet.Cells.CreateRange(0, 0, 1, dt.Columns.Count).ColumnWidth = 18;
            sheet.Cells.Rows[0].Height = 20;
        }

        public void ImportDataTable(int sheetIndex, System.Data.DataTable dt, int firstRow = 0, int firstColumn = 0)
        {
            Worksheet sheet = _Workbook.Worksheets[sheetIndex];
            sheet.Cells.ImportDataTable(dt, true, firstRow, firstColumn);
        }

        public void ImportDataTable(Dictionary<string, System.Data.DataTable> sheets)
        {
            foreach (var sh in sheets)
            {
                var index = AddWorksheet(sh.Key);
                if (index > -1)
                {
                    ImportDataTable(index, sh.Value);
                }
            }
        }

        /// <summary>
        /// 导入自定义对象
        /// </summary>
        /// <param name="sheetIndex">索引</param>
        /// <param name="list">自定义对象集合</param>
        /// <param name="propertyNames">导出和显示的对象属性名称和显示名称，key：导出的属性名称，value：导出显示名称</param>
        /// <param name="dateFormat">日期格式化</param>
        public void ImportCustomObjects<T>(int sheetIndex, List<T> list, Dictionary<string, string> propertyNames, string dateFormat = "yyyy-MM-dd")
        {
            Worksheet sheet = _Workbook.Worksheets[sheetIndex];
            sheet.Cells.ImportArray(propertyNames.Values.ToArray(), 0, 0, false);
            var pNames = propertyNames.Keys.ToArray();
            sheet.Cells.ImportCustomObjects(list, pNames, false, sheet.Cells.Rows.Count, 0, list.Count, true, dateFormat, false);
            sheet.Cells.CreateRange(0, 0, 1, propertyNames.Count).SetStyle(style1);
            sheet.Cells.CreateRange(0, 0, 1, propertyNames.Count).ColumnWidth = 18;
            sheet.Cells.Rows[0].Height = 20;
        }

        /// <summary>
        /// 导出Execl数据到DataSet中
        /// </summary>
        /// <returns></returns>
        public DataSet ExportDataTable(int totalColumns = 0)
        {
            var dataSet = new System.Data.DataSet();
            foreach (Worksheet sheet in _Workbook.Worksheets)
            {
                if (sheet.Cells.Rows.Count == 0)
                    continue;
                var dt = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.Rows.Count, totalColumns == 0 ? sheet.Cells.Columns.Count : totalColumns);
                if (dt != null)
                    dataSet.Tables.Add(dt);
            }

            return dataSet;
        }

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dsExcel">要保存为EXCEL的数据集,可包含多个Table,每个Tabel对应生成一个Sheet页  </param>
        /// <param name="savePath">文件保存路径+文件名 </param>
        public void ExportToExcel(DataSet dsExcel, string savePath, bool isCreateHead = true)
        {
            //创建一个workbook和worksheet对象
            Worksheet wkSheet = null;

            _Workbook.Worksheets.Clear();

            //遍历DataSet
            for (int i = 0; i < dsExcel.Tables.Count; i++)
            {
                //获取DataTable
                DataTable table = dsExcel.Tables[i];

                //创建一个worksheet
                _Workbook.Worksheets.Add(table.TableName);

                //获取worksheet
                wkSheet = _Workbook.Worksheets[i];
                wkSheet.AutoFitColumns();

                //遍历Table的行
                for (int rNum = 0; rNum < table.Rows.Count; rNum++)
                {

                    //遍历Table的列
                    for (int cNum = 0; cNum < table.Columns.Count; cNum++)
                    {
                        if (isCreateHead)
                        { //给sheet写入标头,只需执行一次
                            if (rNum == 0)
                            {
                                string _columnName = table.Columns[cNum].ColumnName.ToString().Trim();

                                //如果DataTable列名包含Column，则表示此列名是由系统自动生成，导出时将列名还原为空值
                                if (_columnName.Contains("Column"))
                                {
                                    _columnName = "";
                                }
                                wkSheet.Cells[0, cNum].PutValue(_columnName);
                            }
                        }
                        //给sheet写入数据
                        wkSheet.Cells[rNum + (isCreateHead?1:0), cNum].PutValue(table.Rows[rNum][cNum].ToString().Trim());
                    }
                }
            }

            //导出保存
            _Workbook.Save(savePath);



        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <typeparam name="T">数据对象</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="columns">要显示的列名和列标题</param>
        /// <param name="sheetName">Excel的表名</param>
        /// <param name="savePath">保存路径</param>
        public void ExportToExcel<T>(IList<T> list, IDictionary<string, string> columns, string sheetName, string savePath)
        {
            if (list.Count > 0)
            {
                try
                {
                    //创建一个workbook和worksheet对象
                    Worksheet wkSheet = null;
                    _Workbook.Worksheets.Clear();
                    //创建一个worksheet
                    _Workbook.Worksheets.Add(sheetName);
                    //获取worksheet
                    wkSheet = _Workbook.Worksheets[0];

                    Type entityType = typeof(T);
                    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);
                    int row = 0, col = 0;

                    foreach (T item in list)
                    {

                        foreach (KeyValuePair<string, string> kvp in columns)
                        {

                            PropertyDescriptor propertyDescriptor = properties.Find(kvp.Key, true);
                            if (propertyDescriptor.Name.ToLower().Equals(kvp.Key.ToLower()))
                            {
                                if (row == 0)
                                {
                                    string columnCaption = kvp.Value;
                                    if (String.IsNullOrWhiteSpace(columnCaption))
                                    {
                                        columnCaption = propertyDescriptor.Description;
                                    }

                                    wkSheet.Cells[0, col].PutValue(columnCaption);
                                }
                                object rowValue = propertyDescriptor.GetValue(item) == null ? DBNull.Value : propertyDescriptor.GetValue(item);
                                wkSheet.Cells[row + 1, col].PutValue(rowValue.ToString());
                                col++;
                            }

                        }

                        col = 0;
                        row++;

                    }
                    wkSheet.Cells.CreateRange(0, 0, 1, columns.Keys.Count).SetStyle(style1);
                    wkSheet.Cells.CreateRange(1, 0, list.Count, columns.Keys.Count).SetStyle(style2);
                    wkSheet.AutoFitColumns();
                    //导出保存
                    _Workbook.Save(savePath);
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex.Message,ex);

                }

            }


        }

        public void Save(string filePath)
        {
            _Workbook.Save(filePath);
        }
        public void Save(System.IO.Stream stream)
        {
            _Workbook.Save(stream, SaveFormat.Xlsx);
        }


    }
}
